﻿<!DOCTYPE html>
<html>
<head>
    <title>jSQL Test Update/Delete</title>
    <script type="text/javascript" src="scripts/jSQL.js"></script>
</head>
<body>
    
    <div id="results"></div>

    <script type="text/javascript">

        jSQL.options.validate = true;
        jSQL.options.validateTableItems = true;

        var oq = outputQry;
        var results = document.getElementById("results");
        

        // Excuse the mess, they're in no particular order but most of the functionality is tested.
        document.addEventListener("DOMContentLoaded", function (event) {
            testCascadeDelete();
            testUpdateWithReplacementObject();
            testDeleteNoJoin();
            testDelete();
            testDeleteMultipleWheres();
            testDeleteNoJoinMultipleWheres();
            testUpdateNoJoin();
            testUpdate();
            testUpdateMultipleWheres();
            testUpdateNoJoinMultipleWheres();
            testUpdateAndDeleteOrdersWithNoProducts();
            testUpdateAndDeleteDangledProducts();
            testValidateDeleteAndUpdate();
            testInvalidUpdateFunction();
            // Used to ensure that the results stay the say when changes are made to the script.
            console.log(hashString(results.innerHTML));
        });

        function testCascadeDelete() {
            logH("testCascadeDelete()");

            var orders = [{ id: 1, address: "1 street" }, { id: 2, address: "2 street TODELETE" },
                        { id: 3, address: "3 street TODELETE" }, { id: 4, address: "4 street" }];
            var orderItems = [{ id: 10, orderId: 1, productId: "p1", qty: 2 },
                              { id: 20, orderId: 2, productId: "TO DELETE", qty: 3 },
                              { id: 21, orderId: 2, productId: "TO DELETE", qty: 3 },
                              { id: 30, orderId: 3, productId: "TO DELETE", qty: 4 },
                              { id: 31, orderId: 3, productId: "TO DELETE", qty: 4 },
                              { id: 40, orderId: 4, productId: "p4", qty: 4 }
            ];
            
            oq(orders);
            oq(orderItems);

            var q1 = jSQL.qry().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").where("x.o.id === 2 || x.o.id === 3");
            oq(q1);

            // Cascade delete the order items.
            q1.remove("o", "oi");

            oq(orders);
            oq(orderItems);
        }

        function testUpdateWithReplacementObject() {
            logH("testUpdateWithReplacementObject()");

            var orders, orderItems;
            function initTables() {
                orders = [{ id: 1, address: "1 street" }, { id: 2, address: "2 street" },
                          { id: 3, address: "3 street" }, { id: 4, address: "4 street" }];
                orderItems = [{ id: 10, orderId: 1, name: "1" },
                              { id: 20, orderId: 2, name: "2.1" },
                              { id: 21, orderId: 2, name: "2.2" },
                              { id: 30, orderId: 3, name: "3" },
                              { id: 40, orderId: 4, name: "4" }
                ];
            }
            
            initTables();
            outputQry(["BEFORE change 2 street -> 2 street NEW"]);
            outputQry(orders);
            jSQL.qry().from(orders).where("x.id === 2").update({ id: 2, address: "2 street NEW" });
            outputQry(["AFTER"]);
            outputQry(orders);

            initTables();
            outputQry(["BEFORE change 2 street -> 2 street NEW and it's items -> id: 99, orderId: 99, name: '99'"]);
            outputQry(orders);
            outputQry(orderItems);
            jSQL.qry().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id")
                                        .where("x.o.id === 2")
                                        .update({ o: { id: 2, address: "2 street NEW" }, oi: { id: 99, orderId: 99, name: "99" }});
            outputQry(["AFTER"]);
            outputQry(orders);
            outputQry(orderItems);

            try {
                jSQL.qry().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id").update({ id: 2, address: "2 street NEW" });
            } catch (e) {
                logError(e, "join table but no alias specified in update.");
            }

            try {
                jSQL.qry().from(orders, "o").update({ o: { id: 2, address: "2 street NEW" }, X1: { id: 99, orderId: 99, name: "99" } });
            } catch (e) {
                logError(e, "non-existent alias.");
            }

            try {
                jSQL.qry().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.o.id")
                                            .update({ o: { id: 2, address: "2 street NEW" }, X2: { id: 99, orderId: 99, name: "99" } });
            } catch (e) {
                logError(e, "non-existent alias, with join.");
            }

        }

        function testUpdateAndDeleteOrdersWithNoProducts() {
            logH("testUpdateAndDeleteOrdersWithNoProducts()");

            var qry1;
            var orders = [{ id: 1, address: "1 street" }, { id: 2, address: "2 street" },
                         { id: 3, address: "3 street" }, { id: 4, address: "4 street" }];
            var orderItems = [{ id: 10, orderId: 1, productId: "p1", qty: 2 },
                              { id: 11, orderId: 1, productId: "p3", qty: 3 },
                              { id: 20, orderId: 2, productId: "NoProduct", qty: 3 },
                              { id: 30, orderId: 3, productId: "p3", qty: 4 },
                              { id: 40, orderId: 4, productId: "NoProduct2", qty: 4 }
            ];
            var products = [{ id: "p1", name: "product 1", type: 11 }, { id: "p2", name: "product 2", type: 22 },
                            { id: "p3", name: "product 3", type: 55 }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders, "o").join(orderItems, "oi", function (x) { return x.o.id === x.oi.orderId; })
                       .leftjoin(products, "p", function (x) { return x.p.id === x.oi.productId; })
                       .where(function (x) { return x.p.id === null });

            outputQry(["TOUPDATE/DELETE"]);
            outputQry(qry1.toArray());

            qry1.update('w => w.o.address = "NOPRODUCT!"');
            outputQry(["AFTER"]);
            outputQry(orders);

            qry1.remove("o");
            outputQry(["AFTER DELETE"]);
            outputQry(orders);
        }

        function testInvalidUpdateFunction() {
            logH("testInvalidUpdateFunction()");

            var orders = [{ id: 1, address: "1 street" }];
            try { jSQL.qry().from(orders).update(); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(undefined); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(null); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update({}); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(10); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update([1, 2]); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(true); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(NaN); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(0 / 0); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(1 / 0); } catch (e) { logError(e); }
            try { jSQL.qry().from(orders).update(-1 / 0); } catch (e) { logError(e); }
        }

        function testUpdateAndDeleteDangledProducts() {
            logH("testUpdateAndDeleteDangledProducts()");

            var qry1, orders = [], orderItems = [], products = [];

            function initTables() {
                orders.length = 0;
                orders.push({ id: 1, address: "1 street" }, { id: 2, address: "2 street" },
                            { id: 3, address: "3 street" }, { id: 4, address: "4 street" });
                orderItems.length = 0;
                orderItems.push({ id: 10, orderId: 1, productId: "p1", qty: 2 },
                                { id: 11, orderId: 1, productId: "p3", qty: 3 },
                                { id: 20, orderId: 2, productId: "NoProduct", qty: 3 },
                                { id: 30, orderId: 3, productId: "p3", qty: 4 },
                                { id: 40, orderId: 4, productId: "NoProduct2", qty: 4 });
                products.length = 0;
                products.push({ id: "p1", name: "product 1", type: 11 }, { id: "dangled1", name: "dangled1", type: 22 },
                              { id: "p3", name: "product 3", type: 55 },
                              { id: "dangled2", name: "dangled2", type: 55 });
            }

            initTables();

            outputQry(["BEFORE"]);
            outputQry(products);

            qry1 = jSQL.qry().from(products, "p")
                       .leftjoin(orderItems, "oi", "x.oi.productId === x.p.id")
                       .leftjoin(orders, "o", "x.o.id === x.oi.orderId")
                       .where("x.o.id === null");

            outputQry(["TODELETE/UPDATE"]);
            outputQry(qry1.toArray());

            qry1.remove("p");

            outputQry(["AFTER DELETE"]);
            outputQry(products);

            initTables();
            qry1.update("x.p.id = 'UPDATED'");
            outputQry(["AFTER UPDATE"]);
            outputQry(products);

        }

        function testValidateDeleteAndUpdate() {
            logH("testValidateDeleteAndUpdate()");

            var orders = [{ id: 1, address: "1 street" }];
            var orderItems = [{ id: 10, orderId: 1, productId: "p1", qty: 2 }];

            try {
                var qry = jSQL.qry().update();
            } catch (e) { logError(e, "UPD: No items specified."); }

            try {
                var qry = jSQL.qry().remove();
            } catch (e) { logError(e, "DEL: No items specified."); }

            try {
                var qry = jSQL.qry().from(orders, "o").remove();
            } catch (e) { logError(e, "DEL: Alias specified + no alias in delete."); }

            try {
                var qry = jSQL.qry().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.id").remove();
            } catch (e) { logError(e, "DEL: Alias specified + join + no alias in delete."); }

            try {
                var qry = jSQL.qry().from(orders, "o").remove("oi");
            } catch (e) { logError(e, "DEL: Alias doesn't exist."); }

            try {
                var qry = jSQL.qry().from(orders, "o").join(orderItems, "oi", "x.oi.orderId === x.id").remove("p");
            } catch (e) { logError(e, "DEL: Alias doesn't exist + join."); }
        }

        function testDeleteMultipleWheres() {
            logH("testDeleteMultipleWheres()");

            var qry1;
            var orders = [{ id: 1, address: "1 street" }, { id: 2, address: "2 street" }, { id: 3, address: "3 street" }];
            var orderItems = [{ id: 10, orderId: 1, productId: "p1", qty: 2 },
                              { id: 11, orderId: 1, productId: "p3", qty: 3 },
                              { id: 20, orderId: 2, productId: "p2", qty: 3 },
                              { id: 30, orderId: 3, productId: "p3", qty: 4 }];
            var products = [{ id: "p1", name: "product 1", type: 11 }, { id: "p2", name: "product 2", type: 22 },
                            { id: "p3", name: "product 3", type: 55 }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders, "o")
                       .join(orderItems, "oi", function (x) { return x.o.id === x.oi.orderId; })
                       .join(products, "p", function (x) {
                           return x.p.id === x.oi.productId;
                       })
                       .where(function (x) { return x.p.type === 55; })
                       .where(function (x) { return x.oi.qty === 3; })
                       .remove("o");

            outputQry(["AFTER"]);
            outputQry(orders);
        }

        function testDeleteNoJoinMultipleWheres() {
            logH("testDeleteNoJoinMultipleWheres()");

            var orders = [{ id: 1, address: "1 street", type: "A" }, { id: 2, address: "2 street", type: "B" },
                          { id: 3, address: "3 street", type: "C" }, { id: 4, address: "4 street", type: "B" }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders)
                       .where(function (x) { return x.type === "B"; })
                       .where(function (x) { return x.address === "2 street"; })
                       .remove();

            outputQry(["AFTER"]);
            outputQry(orders);
        }

        function testUpdateNoJoinMultipleWheres() {
            logH("testUpdateNoJoinMultipleWheres()");

            var qry1;
            var orders = [{ id: 1, address: "1 street", type: "A" }, { id: 2, address: "2 street", type: "B" },
                          { id: 3, address: "3 street", type: "C" }, { id: 4, address: "4 street", type: "B" }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders)
                       .where(function (x) { return x.type === "B"; })
                       .where(function (x) { return x.address === "2 street"; })
                       .update(function (x) { x.address += " TYPE B"; });
            outputQry(["AFTER"]);
            outputQry(orders);

        }

        function testUpdateMultipleWheres() {
            logH("testUpdateMultipleWheres()");

            var qry1;
            var orders = [{ id: 1, address: "1 street" }, { id: 2, address: "2 street" }, { id: 3, address: "3 street" }];
            var orderItems = [{ id: 10, orderId: 1, productId: "p1", qty: 2 },
                              { id: 11, orderId: 1, productId: "p3", qty: 3 },
                              { id: 20, orderId: 2, productId: "p2", qty: 3 },
                              { id: 30, orderId: 3, productId: "p3", qty: 4 }];
            var products = [{ id: "p1", name: "product 1", type: 11 }, { id: "p2", name: "product 2", type: 55 },
                            { id: "p3", name: "product 3", type: 55 }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders, "o")
                       .join(orderItems, "oi", function (x) { return x.o.id === x.oi.orderId; })
                       .join(products, "p", function (x) {
                           return x.p.id === x.oi.productId;
                       })
                       .where(function (x) { return x.p.type === 55; })
                       .where(function (x) { return x.p.name === "product 2"; })
                       .update(function (x) { x.o.address = "UPDATED"; });

            outputQry(["AFTER"]);
            outputQry(orders);
        }

        function testUpdateNoJoin() {
            logH("testUpdateNoJoin()");

            var qry1;
            var orders = [{ id: 1, address: "1 street", type: "A" }, { id: 2, address: "2 street", type: "B" },
                          { id: 3, address: "3 street", type: "C" }, { id: 4, address: "4 street", type: "B" }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders).where(function (x) { return x.type === "B"; });

            outputQry(["TOUPDATE"]);
            outputQry(qry1.toArray());

            qry1.update(function (x) { x.address += " TYPE B"; });

            outputQry(["AFTER"]);
            outputQry(orders);
        }

        function testUpdate() {
            logH("testUpdate()");

            var qry1;
            var orders = [{ id: 1, address: "1 street" }, { id: 2, address: "2 street" }, { id: 3, address: "3 street" }];
            var orderItems = [{ id: 10, orderId: 1, productId: "p1", qty: 2 },
                              { id: 11, orderId: 1, productId: "p3", qty: 3 },
                              { id: 20, orderId: 2, productId: "p2", qty: 3 },
                              { id: 30, orderId: 3, productId: "p3", qty: 4 }];
            var products = [{ id: "p1", name: "product 1", type: 11 }, { id: "p2", name: "product 2", type: 22 },
                            { id: "p3", name: "product 3", type: 55 }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders, "o")
                       .join(orderItems, "oi", function (x) { return x.o.id === x.oi.orderId; })
                       .join(products, "p", function (x) {
                           return x.p.id === x.oi.productId;
                       })
                       .where(function (x) {
                           return x.p.type === 55
                       });

            outputQry(["TOUPDATE"]);
            outputQry(qry1.toArray());

            qry1.update(function (x) { x.o.address = "PTYPE=55"; });

            outputQry(["AFTER"]);
            outputQry(orders);
        }

        function testDelete() {
            logH("testDelete()");

            var qry1;
            var orders = [{ id: 1, address: "1 street" }, { id: 2, address: "2 street" }, { id: 3, address: "3 street" }];
            var orderItems = [{ id: 10, orderId: 1, productId: "p1", qty: 2 },
                              { id: 11, orderId: 1, productId: "p3", qty: 3 },
                              { id: 20, orderId: 2, productId: "p2", qty: 3 },
                              { id: 30, orderId: 3, productId: "p3", qty: 4 }];
            var products = [{ id: "p1", name: "product 1", type: 11 }, { id: "p2", name: "product 2", type: 22 },
                            { id: "p3", name: "product 3", type: 55 }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders, "o")
                       .join(orderItems, "oi", function (x) { return x.o.id === x.oi.orderId; })
                       .join(products, "p", function (x) {
                           return x.p.id === x.oi.productId;
                       })
                       .where(function (x) {
                           return x.p.type === 55
                       });

            outputQry(["TODELETE"]);
            outputQry(qry1.toArray());

            qry1.remove("o");

            outputQry(["AFTER"]);
            outputQry(orders);
        }

        function testDeleteNoJoin() {
            logH("testDeleteNoJoin()");

            var qry1;
            var orders = [{ id: 1, address: "1 street", type: "A" }, { id: 2, address: "2 street", type: "B" },
                          { id: 3, address: "3 street", type: "C" }, { id: 4, address: "4 street", type: "B" }];

            outputQry(["BEFORE"]);
            outputQry(orders);

            qry1 = jSQL.qry().from(orders).where(function (x) { return x.type === "B"; });

            outputQry(["TODELETE"]);
            outputQry(qry1.toArray());

            qry1.remove();

            outputQry(["AFTER"]);
            outputQry(orders);
        }

        function logError(e, msg) {
            if (msg) {
                msg = "[" + msg + "]: " + e.name + ": " + e.message;
            }
            else {
                msg = e.name + ": " + e.message;
            }

            log(msg);
        }

        function log(msg) {
            msg += "";
            msg = msg.split("\n").join("<br />");
            append("<p>" + msg + "</p>");
        }

        function logH(msg) {
            msg += "";
            msg = msg.split("\n").join("<br />");
            append("<p style='color:green;font-weight:bold'>" + msg + "</p>");
        }

        function outputQry(qry) {
            var sb = "";
            if (qry.length === undefined) {
                qry = qry.toArray();
            }
            for (var i = 0; i < qry.length; i++) {
                if (sb.length > 0) {
                    sb += "<br />";
                }
                sb += JSON.stringify(qry[i]);
            }
            append("<p>" + sb + "</p>");
        }
        
        var hashString = function (str) {
            var h = 0, i = 0, l = str.length;
            if (l === 0) return h;
            for (; i < l; i++) {
                h = ((h << 5) - h) + str.charCodeAt(i);
                h |= 0; // Convert to 32bit integer
            }
            return h;
        };

        function append(html) {
            results.innerHTML += html;
        }

    </script>

</body>
</html>